package org.px.service;

import org.px.domain.LeaveRecord;
import org.px.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.handlers.MapListHandler;

public class LeaveRecordService {
    private QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    /**
     * 添加请假记录
     */
    public boolean addLeaveRecord(LeaveRecord leaveRecord) throws SQLException {
        String sql = "INSERT INTO leave_application (emp_id, leave_type, start_time, end_time, reason, apply_time) " +
                    "VALUES (?, ?, ?, ?, ?, NOW())";
        Object[] params = {
                leaveRecord.getEmp_id(),
                leaveRecord.getLeave_type(),
                leaveRecord.getStart_time(),
                leaveRecord.getEnd_time(),
                leaveRecord.getReason()
        };
        int count = qr.update(sql, params);
        return count > 0;
    }

    /**
     * 根据员工ID获取请假记录
     */
    public List<LeaveRecord> getLeaveRecordsByEmpId(String empId) throws SQLException {
        String sql = "SELECT * FROM leave_application WHERE emp_id = ?";
        return qr.query(sql, new BeanListHandler<>(LeaveRecord.class), empId);
    }

    /**
     * 根据状态获取请假记录
     */
    public List<LeaveRecord> getLeaveRecordsByStatus(String status) throws SQLException {
        String sql = "SELECT * FROM leave_application WHERE status = ?";
        return qr.query(sql, new BeanListHandler<>(LeaveRecord.class), status);
    }

    /**
     * 获取所有请假记录
     */
    public List<LeaveRecord> getAllLeaveRecords() throws SQLException {
        String sql = "SELECT * FROM leave_application";
        return qr.query(sql, new BeanListHandler<>(LeaveRecord.class));
    }

    /**
     * 更新请假记录状态
     */
    public boolean updateLeaveRecordStatus(int leaveId, String status, String user_id) throws SQLException {
        String sql = "UPDATE leave_application SET status = ?, approver_id = ? WHERE leave_id = ?"; // 移除多余括号
        int count = qr.update(sql, status, user_id, leaveId);
        return count > 0;
    }

    /**
     * 获取指定日期各部门的请假人数统计
     */
    public List<Map<String, Object>> getDepartmentLeaveStatistics(java.util.Date date) throws SQLException {
        String sql = "SELECT d.dept_name, COUNT(DISTINCT l.emp_id) AS leave_count " +
                    "FROM department d " +
                    "LEFT JOIN employee e ON d.dept_id = e.dept_id " +
                    "LEFT JOIN leave_application l ON e.emp_id = l.emp_id " +
                    "   AND ? BETWEEN l.start_time AND l.end_time " +
                    "GROUP BY d.dept_id, d.dept_name";
        return qr.query(sql, new MapListHandler(), new java.sql.Date(date.getTime()));
    }

    // 删除请假记录方法
    public boolean deleteLeaveRecord(int leaveId, String userId) throws SQLException {
        // 安全检查：只允许删除待审批状态的请假记录
        String checkSql = "SELECT status FROM leave_application WHERE leave_id = ?";
        String status = qr.query(checkSql, rs -> rs.next() ? rs.getString("status") : null, leaveId);
        
        if (status == null) {
            throw new SQLException("请假记录不存在");
        }
        
        if (!"待审批".equals(status)) {
            return false; // 只能删除待审批状态的记录
        }
        
        String deleteSql = "DELETE FROM leave_application WHERE leave_id = ?";
        int count = qr.update(deleteSql, leaveId);
        return count > 0;
    }
}